package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.NewsDetails;
import com.ysd.util.DBManager;

public class NewsDetailsDaoImpl {

	public List<NewsDetails> getNDetails(int cId){
		List<NewsDetails> newsList = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails where newsclassifyId="+cId);
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				NewsDetails nDetails = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
				newsList.add(nDetails);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<NewsDetails> getNewDetails(int nId){
		List<NewsDetails> newsList = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails where newsDetailsId="+nId);
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				NewsDetails nDetails = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
				newsList.add(nDetails);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<NewsDetails> getMore(){
		List<NewsDetails> moreList = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails");
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				NewsDetails MoreDetails = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
				moreList.add(MoreDetails);
			}
			return moreList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<NewsDetails> skipNewDetails(int aId){
		List<NewsDetails> newsList = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails where newsDetailsId = (select newsDetailsId from newsdetails where newsDetailsId < "+aId+" order by newsDetailsId desc limit 1)"); 
					
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				NewsDetails nDetails = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
				newsList.add(nDetails);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<NewsDetails> skipNewsDetails(int bId){
		List<NewsDetails> newsList = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails where newsDetailsId = (select newsDetailsId from newsdetails where newsDetailsId > "+bId+" order by newsDetailsId limit 1)"); 
					
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				NewsDetails nDetails = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
				newsList.add(nDetails);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 根据咨询详情返回的Id删除产品
	 * @param id newsDetailsId
	 * @return 数据库受影响行数
	 */
	public int delNewsDetails(int newsId) {
		return DBManager.executeUpdate("delete from newsdetails where newsDetailsId="+newsId);
	}
	
	
	/**
	 * 多条件查询咨询详情
	 * 返回list集合
	 */
	public List<NewsDetails> getNewsByWhere(NewsDetails n){
		List<NewsDetails> list = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			//多条件查询时,条件可能不写,sql不能写死,需要通过条件判断拼接
			String sql = "select nd.*,nf.newsClassifyName from newsdetails nd,newsclassify nf where 1=1";
			//非空判定
			if(n.getNewsDetailsId()!=0) {
				sql += " and newsDetailsId=" + n.getNewsDetailsId();
			}
			if(n.getTitle()!=null && !n.getTitle().equals("")) {
				sql += " and title like '%"+n.getTitle()+"%'";
			}
			if(n.getIntroduction()!=null && !n.getIntroduction().equals("")) {
				sql += " and introduction like '%"+n.getIntroduction()+"%'";
			}
			if(n.getContent()!=null && !n.getContent().equals("")) {
				sql += " and content like '%"+n.getContent()+"%'";
			}
			if(n.getNewsClassifyId()!=0) {
				sql += " and newsClassifyId=" + n.getNewsClassifyId();
			}
			sql += " and nd.newsClassifyId=nf.newsCategoryId";
			System.out.println(sql);//打印sql语句
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				String newsClassifyName = rs.getString("newsClassifyName");
				NewsDetails news = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId, newsClassifyName);
				list.add(news);
				
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 多条件分页查询咨询详情
	 * 返回list集合
	 */
	public List<NewsDetails> getNewsByPage(NewsDetails n, int page, int size){
		List<NewsDetails> list = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			//多条件查询时,条件可能不写,sql不能写死,需要通过条件判断拼接
			String sql = "select nd.*,nf.newsClassifyName from newsdetails nd,newsclassify nf where 1=1";
			//非空判定
			if(n.getNewsDetailsId()!=0) {
				sql += " and newsDetailsId=" + n.getNewsDetailsId();
			}
			if(n.getTitle()!=null && !n.getTitle().equals("")) {
				sql += " and title like '%"+n.getTitle()+"%'";
			}
			if(n.getIntroduction()!=null && !n.getIntroduction().equals("")) {
				sql += " and introduction like '%"+n.getIntroduction()+"%'";
			}
			if(n.getContent()!=null && !n.getContent().equals("")) {
				sql += " and content like '%"+n.getContent()+"%'";
			}
			if(n.getNewsClassifyId()!=0) {
				sql += " and newsClassifyId=" + n.getNewsClassifyId();
			}
			sql += " and nd.newsClassifyId=nf.newsCategoryId limit "+(page-1)*size+","+size;
			System.out.println(sql);//打印sql语句
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				String newsClassifyName = rs.getString("newsClassifyName");
				NewsDetails news = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId, newsClassifyName);
				list.add(news);
				
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 根据多字段添加咨询详情
	 * @param ne
	 * @return 受影响行数
	 */
	public int addNewDetails(NewsDetails ne) {
		int n = DBManager.executeUpdate("insert into newsdetails values(null,'"+ne.getTitle()+"','"+ne.getIntroduction()+"','"+ne.getContent()+"','"+ne.getNewsClassifyId()+"')");
		System.out.println(n);
		return n;
	}
	
	public List<NewsDetails> getProDetailsByTitle(String tit){
		List<NewsDetails> newsList = new ArrayList<NewsDetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails where title='"+tit+"'"); 
					
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				NewsDetails nDetails = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
				newsList.add(nDetails);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 根据id查询 
	 * @param newId
	 * @return
	 */
	 
	public NewsDetails selNewsDetailsById(int newId) {
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		NewsDetails nd = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from newsdetails where newsDetailsId=" + newId);
			while(rs.next()) {
				int newsDetailsId = rs.getInt("newsDetailsId");
				String title = rs.getString("title");
				String introduction = rs.getString("introduction");
				String content = rs.getString("content");
				int newsClassifyId = rs.getInt("newsClassifyId");
				nd = new NewsDetails(newsDetailsId, title, introduction, content, newsClassifyId);
			}
			return nd;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 修改数据库字段的值
	 * @param news
	 * @return 新闻详情对象
	 */
	public int updateNewDetailsById(NewsDetails news) {
		String sql = "update newsdetails set title='"+news.getTitle()+"',introduction='"+news.getIntroduction()+"',content='"+news.getContent()+"',newsClassifyId="+news.getNewsClassifyId()+" where newsDetailsId="+news.getNewsDetailsId();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
		
	}
	
	/**
	 * 批量删除
	 * @param ids
	 * @return 多个id为String
	 */
	public int delNewsDetailsByIds(String ids) {
		String sql = "delete from newsdetails where newsDetailsId in("+ids+")";
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
}
